-- devuelve el nuemero de localidades que ocupan un area
CREATE FUNCTION f_num_localidades_en_area() RETURNS integer AS
$$
DECLARE
 cant integer := 0;
BEGIN
 cant:= (SELECT COUNT(id_area) FROM localidad WHERE id_area IS NOT NULL);
 RETURN cant;
END;
$$ LANGUAGE plpgsql;
--se llama con: 
-- SELECT f_num_localidades_en_area();

-- Devolver algunos detalles de localidades que se han definido dentro un area
CREATE OR REPLACE FUNCTION f_localidades_definidas_en_area(OUT id_area integer, OUT id_punto integer, OUT latitud float, OUT longitud float)
RETURNS setof record AS
$BODY$
BEGIN
	RETURN QUERY SELECT l.id_area AS id_area, p.id_punto_area AS id_punto, p.latitud, p.longitud
	       	     FROM localidad l, punto_area p
		     WHERE l.id_area IS NOT NULL  AND  p.id_area = l.id_area;
	RETURN;
END;
$BODY$
LANGUAGE plpgsql;
-- se llama con:
--  select * from f_localidades_definidas_en_area();

-- ------ Insercion Segura de usuarios --------
CREATE OR REPLACE FUNCTION f_insertar_usuario(nom varchar, pas varchar, e_mail varchar, tip integer, datos varchar)
RETURNS integer AS
$BODY$
DECLARE 
	num integer := 0;
BEGIN
	num := (SELECT COUNT(nombre) FROM usuario WHERE nombre = nom);
	IF num>=1 THEN
	   RETURN -1; -- error no puede haber otro usuario con el mismo nombre		
	END IF;
	
	INSERT INTO usuario (nombre, pass, email, tipo, fecha_registro, datos_adicionales) 
	VALUES (nom, pas, e_mail, tip, CURRENT_DATE ,datos);

	RETURN 0; -- Ok
END;
$BODY$ LANGUAGE plpgsql;
--se llama con por ejemplo:
-- SELECT f_insertar_usuario('Rick', '12354','Rick@ui.net', 1, 'algo mas');

-- ## Listar 'todo' en area cuadrada ##
-- Comprueba si las coordendas dadas estan dentro de x1,y1 x2,y2 (area cuadrada)
CREATE OR REPLACE FUNCTION f_comprobar_coordenadas_dentro_de_area(latitud float, longitud float, 
       	  	  	   					x1 float, y1 float, x2 float, y2 float ) RETURNS boolean AS 
$BODY$
DECLARE
	X_sup_izq float :=0;
	Y_sup_izq float :=0;
	X_inf_der float :=0;
	Y_inf_der float :=0;
BEGIN	
	/* se espera
			(x1,y1)--------- 
			|		|
			|		|
			|		|
			 ------------(x2,y2)
	   si no se corrije
	*/
	-- comprueba puntos superior izquierdo e inferior derecho
	IF x1>x2 THEN
	   X_sup_izq := x2;	
	   X_inf_der := x1;
	ELSE
	   X_sup_izq := x1;
	   X_inf_der := x2;
	END IF;
	IF y1<y2 THEN
	   Y_sup_izq := y2;
	   Y_inf_der := y1;
	ELSE
	   Y_sup_izq := y1;
	   Y_inf_der := y2;
	END IF;
	-- comprueba si las coordenadas dadas estan dentro el area
	IF longitud>=X_sup_izq AND longitud<=X_inf_der AND latitud<=Y_sup_izq AND latitud>=Y_inf_der THEN
	   RETURN true;	
	END IF;
	RETURN false;
END;
$BODY$ LANGUAGE plpgsql;
--se llama con
-- SELECT f_comprobar_coordenadas_dentro_de_area(-17.232, -67.8000, -66.500, -16.200, -68.5, -18.15);
-- SELECT f_comprobar_coordenadas_dentro_de_area(-17.232, -67.8000, -69.500, -16.200, -68.5, -18.15);

-- comprobar si un/as localidad/es esta\n dentro de un area dada (x1,y1 x2,y2), si esta\n devuelve la/s localidad/es
CREATE OR REPLACE FUNCTION f_localidad_dentro_de_area(x1 float, y1 float, x2 float, y2 float) 
RETURNS setof localidad AS
$BODY$
DECLARE
	rec localidad%rowtype;
	ea boolean := false;
BEGIN
	-- iterando en cada localidad
	FOR rec IN SELECT * FROM localidad LOOP 
	    -- TODO: agregar deteccion de areas no nulas
	    ea := (SELECT f_comprobar_coordenadas_dentro_de_area(rec.latitud, rec.longitud, x1, y1, x2, y2));
	    IF ea THEN
	       RETURN NEXT rec;	-- apila la localidad en el 'arreglo de localidades'
	    END IF;
	END LOOP;
	RETURN;	-- devuelve el arreglo de localidades
END;
$BODY$ LANGUAGE plpgsql;
--se llama con
-- SELECT * FROM f_localidad_dentro_de_area(-67.23, -16.7, -67.800, -16.1);
-- SELECT * FROM f_localidad_dentro_de_area(-88.23, -16.7, -97.800, -16.1);

-- comprobar si hay lugar/es dentro de un area dada (x1,y1 x2,y2), si hay devuelve el/los lugar/es
CREATE OR REPLACE FUNCTION f_lugar_dentro_de_area(x1 float, y1 float, x2 float, y2 float)
RETURNS setof lugar AS
$BODY$
DECLARE
	rec lugar%rowtype;
	ea boolean := false;
BEGIN
	-- iterando en cada lugar
	FOR rec IN SELECT * FROM lugar LOOP 
	    -- TODO: agregar deteccion de areas no nulas
	    ea := (SELECT f_comprobar_coordenadas_dentro_de_area(rec.latitud, rec.longitud, x1, y1, x2, y2));
	    IF ea IS TRUE THEN
	       RETURN NEXT rec;	-- apila el lugar en el 'arreglo de lugares'
	    END IF;
	END LOOP;
	RETURN;	-- devuelve el arreglo de lugares
END;
$BODY$ LANGUAGE plpgsql;
--se llama con
-- SELECT * FROM f_lugar_dentro_de_area(-67.23, -16.7, -67.800, -16.1);
-- SELECT * FROM f_lugar_dentro_de_area(-88.23, -16.7, -97.800, -16.1);

-- comprobar si hay parada/s dentro de un area dada (x1,y1 x2,y2), si hay devuelve parada/s 
CREATE OR REPLACE FUNCTION f_parada_dentro_de_area(x1 float, y1 float, x2 float, y2 float)
RETURNS setof parada AS
$BODY$
DECLARE
	rec parada%rowtype;
	ea boolean := false;
BEGIN
	-- iterando en cada parada
	FOR rec IN SELECT * FROM parada LOOP 
	    ea := (SELECT f_comprobar_coordenadas_dentro_de_area(rec.latitud, rec.longitud, x1, y1, x2, y2));
	    IF ea IS TRUE THEN
	       RETURN NEXT rec;	-- apila el lugar en el 'arreglo de paradas'
	    END IF;
	END LOOP;
	RETURN;	-- devuelve el arreglo de paradas
END;
$BODY$ LANGUAGE plpgsql;
--se llama con
-- SELECT * FROM f_parada_dentro_de_area(-67.23, -16.7, -67.800, -16.1);
-- SELECT * FROM f_parada_dentro_de_area(-88.23, -16.7, -97.800, -16.1);

-- comprobar si hay actividad/es dentro de un area (x1,y1, x2,y2), si hay devuelve actividad/es'
CREATE OR REPLACE FUNCTION f_actividad_dentro_de_area(x1 float, y1 float, x2 float, y2 float)
RETURNS setof actividad AS
$BODY$
DECLARE
	rec RECORD; -- cualquier tipo
	ea boolean := false;
BEGIN
	-- iterando en cada parada
	FOR rec IN SELECT l.latitud, l.longitud, a.id_actividad as idact, a.id_localidad
	    	   FROM actividad a, localidad l WHERE a.id_localidad = l.id_localidad
	LOOP
	    ea := (SELECT f_comprobar_coordenadas_dentro_de_area(rec.latitud, rec.longitud, x1, y1, x2, y2));
	    IF ea IS TRUE THEN
	       RETURN QUERY (SELECT * FROM actividad WHERE id_actividad = rec.idact); -- apila query (actividad)
	    END IF;
	END LOOP;
	RETURN;	-- devuelve el arreglo
END;
$BODY$ LANGUAGE plpgsql;
--se llama con
-- SELECT * FROM f_actividad_dentro_de_area(-67.23, -16.7, -67.800, -16.1);
-- SELECT * FROM f_actividad_dentro_de_area(-88.23, -16.7, -97.800, -16.1);


-- comprobar si hay 'medios de transporte' cuyas paradas estan dentro de un area dada (x1,y1 x2,y2), si hay devuelve medio/s de transpore
CREATE OR REPLACE FUNCTION f_medio_transporte_dentro_de_area(x1 float, y1 float, x2 float, y2 float)
RETURNS setof medio_transporte AS
$BODY$
DECLARE
	paradarec RECORD; -- cualquier tipo
	ea boolean := false;
BEGIN
	-- iterando en cada parada
	FOR paradarec IN SELECT * FROM f_parada_dentro_de_area(x1, y1, x2, y2) LOOP
	    ea := (SELECT f_comprobar_coordenadas_dentro_de_area(paradarec.latitud, paradarec.longitud, x1, y1, x2, y2));
	    IF ea IS TRUE THEN
	    	RETURN QUERY (select mt."id_medioT", mt.id_lugar, mt.tipo, mt.descr 
			       FROM medio_transporte mt, medio_transporteparada mtp, parada p 
			       WHERE
			       mt."id_medioT" = mtp."id_medioT" AND mtp.id_parada=p.id_parada AND p.id_parada = paradarec.id_parada);
	    END IF;
	END LOOP;
	RETURN;	-- devuelve el arreglo de 'medio_transporte'

	-- FIXME: Ver como no retornar valores repetidos (tal vez iterando con for en lugar de apilar query)
END;
$BODY$ LANGUAGE plpgsql;
--se llama con
-- SELECT * FROM f_medio_transporte_dentro_de_area(-67.23, -16.7, -67.800, -16.1);
-- SELECT * FROM f_medio_transporte_dentro_de_area(-88.23, -16.7, -97.800, -16.1);

-- comprobar si el punto dado esta dentro el area (id_area)
-- TODO: implementar la solucion de http://alienryderflex.com/polygon/ para hallar puntos dentro de poligonos
CREATE OR REPLACE FUNCTION f_area_contiene_a_coordenada(latitud float, longitud float, id_de_area integer)
RETURNS boolean AS
$BODY$
DECLARE
	puntorec punto_area%rowtype;
BEGIN
	FOR puntorec
END;
$BODY$ LANGUAGE plgpsql;



-- -------------- TRIGGERS ---------------------
-- Evitar que se introduzcan usuarios duplicados
-- TODO: terminar
CREATE OR REPLACE FUNCTION t_insertar_usuario_duplicado() RETURNS trigger AS 
$BODY$
DECLARE
	coun integer :=0;	
BEGIN
	coun := (SELECT nombre)
END;
$BODY$ LANGUAGE plpgsql;

-- Ejemplos (de otras BD) --
CREATE OR REPLACE FUNCTION VentaCajero (IN fechaReporte date, OUT  Cajero text, OUT total_venta double precision) 
RETURNS setof record AS 
$BODY$
BEGIN
return query SELECT NOMBRE||' '||PATERNO||' '||MATERNO AS CAJERO, SUM(V.MONTO) AS TOTAL_VENTA FROM VENTA V, EMPLEADO E WHERE V.CODCAJ=E.CI AND V.FECHA = fechaReporte GROUP BY CAJERO;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql'; 
